LebGeeks

battikh

Member

SQL problem

helloooow.
i have a little problem in mysql.
i have, lets say, 3 tables: table1, table2, table3
in each table i have a field called "name"

if i want to get all the different names from the 3 tables i am using the following query: "SELECT DISTINCT table1.name, table2.name, table3.name FROM table1, table2, table3"
but this gets me nothing while if i only put: "SELECT DISTINCT table1.name FROM table1" it gets me some results.
what am i doing wrong?

mir

Member

Re: SQL problem

well the first thing u did wrong
was while designing ur database
y did u create 3 tables, each containing names of customers (it is a guess )
you should have created a customer table and put customer id in table1, table2,table3

if table1 contains a,b,c
table 2 : a,b,d,e,f,g
talbe3 : a,b,f,g,h,i
what u will get from the above select is a list containing
table1 with ( table2 with all the records of table3 )

maybe u should try using the NOT IN operator

maybe if u tell me what u are trying to do, i will be able to help u more
r u now doing reports ?
r u using Crystal Report for example ?

hope i can help u ! post what u are trying to do

battikh

Member

Re: SQL problem

mmm, yeah, my tables might be screwed up and badly designed, i did them without really thinking and designing
but even ifit is the case, it should be doneable, i dont understand why it is'nt working.

here is what i am doing (nothing related to names or customers ):
i have like 5 switches
for each switch i did a table containing these fields :port#,company,vlan,patch#
to know each port is used by which company and connected to which patch and on which vlan.
this way, i would have all the informations i need about each switch in a table. i did a graphical interface to show all teh info and modify them,... all is working great.

but i wanted to add an option, for example to click on a company and list all the ports they are connected to on the different switches. so i need frst to get a list of all the companies that are in the tables

but yeah, now that u said it, i see that it would have been cleverer to add a table containing teh companies, another one for the vlans,... and just add the ids in the correspondant fields, bass yalla, if i can bypass this, i prefere, i don't want to redo and repair everything unless it's necessary

in the way my tables currently are, if
in table1 i have a,b,d,e
in table2 i have b,c,f,g,h
in table3 i have a,f,i

i want a query that returns me a,b,c,d,e,f,g,h,i
is'nt the query i use supposed to do that? it return me NOTHING

Last edited by battikh (October 2 2006)

teodorgeorgiev

Member

Re: SQL problem

:)

Yes, it is not going to work that way.

Two possibilities here:

1. Use a nested (complex) SELECT.
2. Use two selects with the help of a temporary table. Select all the usernames from the 3 table fields into one new table field and then do a DISTINCT select over the temporary table.

battikh

Member

Re: SQL problem

:)

Yes, it is not going to work that way.

Two possibilities here:

1. Use a nested (complex) SELECT.
2. Use two selects with the help of a temporary table. Select all the usernames from the 3 table fields into one new table field and then do a DISTINCT select over the temporary table.

even if i just do "SELECT * FROM table1,table2,table3" i get nothing, even without the distinct...

zis

Member

Re: SQL problem

You must use a left joint. look it up. i don't have time to write more abt it now.. tonight i can give you more details.

mir

Member

Re: SQL problem

but yeah, now that u said it, i see that it would have been cleverer to add a table containing teh companies, another one for the vlans,... and just add the ids in the correspondant fields, bass yalla, if i can bypass this, i prefere, i don't want to redo and repair everything unless it's necessary

well it is ur decision if u want to redo part of the work or stay with the current database
i guess this depends on how much u are going to use the system u did and future development
if u are going to use it a lot and for long time.. u better start something new and clean

can i suggest something :

you can create a table where u can collect the name of the companies
in the switch table.. u don't have to link to the companies Id. just leave it like it is
and everytime u insert new company.make sure to insert it automatically in companies table
like that you don't have to write a long running query every time u want a list of the companies
or every time u want to query something about companies

it is just a suggestion.. u can follow or u can not

now i am just too lazy to think about anything :) ..

battikh

Member

Re: SQL problem

zis, i tried a little with left join, with 2 tables, it's ok, it works perfectly, with 3 it gets more complicated, and i dont want to imagine what it will get with 9 tables  i just feel like trying it for teh challenge
but practically, i think i will go with mir's 1st advice, just doing it properly, adding a table for companies and bidoun ter2i3, just doing it right

mir

Member

Re: SQL problem

well doing it right without tir2i3 is the best thing to do :)
now you got something to do  .. (u were complaining about being bored  )

if u want u can post what u need.. and what tables u created
like that u can make sure that no mistakes were made and u don't have to redo the work again

w yalla happy coding
btw what r u using to do ur app ?

battikh

Member

Re: SQL problem

i'm using php and mysql, the usual for me these days

what i think i will be doing for the tables is the following:

switches (a table with the list of switches):
id
name
description
ip

switchslots (the slots that form the switches, each switch is made of multiple slots):
id
switchid (the id of the switch they are part of)
slotnumber (the number it has on the switch)

ports (all the ports of all the switches):
id
patch# (to which patch panel it is connected)
companyid (id of the company that is using it)
vlan (to which vlan it is part of)
comment (in case of comment )

company (all the companies available):
id
name
color (to each company we set a unique color)

so, what do you think? good? no good? any room for improvment?

Last edited by battikh (October 3 2006)

mir

Member

Re: SQL problem

Hi battikh :)
well guess much better now :)

well.. is there any possibility of having a "company --- branch" case
or u enter each branch as a new company and u just know from the name

well i guess that this does what u need to do without complicating stuff a lot
and another little small thing about naming :
for example use PortId, CompanyId,SwitchId helps u a lot when writing query and it is a good and useful naming convention :)