selecting from 2 tables with combining info

[insomniac]

Hi,

i cann't figure it out how to do this.

i have 1 table (art) with info about products
id, main_group_id, group_id,sub_group_id,name,description,price

and 1 table (xtra_art) that has only
id,art_id,main_group_id, group_id,sub_group_id

art_id is the id from the table art.

Now this is what i want.

I want to select everything from both tables where the main_group_id is let say 156. That's not the trick, but when there is a match in xtra_art it has to put the name, description and price from the table art to the corresponding row. So if art_id from xtra_art equals id from art put the data together.

I don't know if you get it, but here is a small example.
table: art
id, main_group_id, group_id,sub_group_id,name,description,price
1 , 156, 23, 5 , hat,nice hat,9.95
2, 155,22,7,pants,blue pants,15.95

table xtra_art
1,1,155,22,7

know i want to select all things from both table with main_group_id equals 155, and put the info to the rows.
This is the output i want:
2, 155,22,7,pants,blue pants,15.95
1 , 155, 22, 7 , hat,nice hat,9.95

where hat,nicehat,9.95 is the info from table art

Thanks in advance,

Jeroen.

[bastien]

select a., b. from art a, xtra_art b where
a.id = b.art_id and a.id = $variable

will select everything from the tables when the values equal and exist. To limit the query to specific fields, just list them with the appropriate table reference. ie

a.name, a.description, a.price, b.id

PS Description is not a good column name to use since it tends to be a reserved keyword in many sql languages. Best to use something like product_description or something like that.

[Roger_Ramjet]

bastien, I know you say that your query will return results where "the values equal and exist", but that is not what was asked for: "i want to select all things from both table with main_group_id equals 155"

What insomniac (like me eh! it's 3:15 am and I'm here posting) needs is a UNION query http://dev.mysql.com/doc/mysql/en/UNION.html .

"SELECT 
art.id as A,
art.main_group_id as B,
art.group_id as C,
art.sub_group_id as D,
art.name as E,
art.description as F,
art.price as G 
FROM art WHERE B=155
UNION
SELECT 
xtra_art.id as A, 
xtra_art.main_group_id as B, 
xtra_art.group_id as C, 
xtra_art.sub_group_id as D, 
art.name as E, 
art.description as F, 
art.price as G
FROM art, xtra_art ON art.id=xtra_art.art_id WHERE B=155"

I have written it out long-style to make the syntax explicit. Also, you can use whatever aliases you want, I used A-G to save keystrokes. Also, I guessed that the first field 'id' is from xtra_art not art, it is not actually obvious which one you want from your example.

Which leads me to my next point: NEVER call any field 'id'. Every field name should be uique across all table in a database. As it is you have id in every table - which one are you refering too? You see my point above.

Use art_id in table art, xtra_id in table xtra_art, then there can be no confusion either for a person or the DBMS.

I personally don't bother to change the name when it is a foreign key in another table, but then I use Access a lot and it will auto-join when the names are the same, just saves me time and the fields contain the same data so it is OK. In principle though, you should vary the name, as you already do, to show that it is a forign key. Use something like art_id in table art and art_id_f in table xtra_art and you won't go far wrong.

[bastien]

You might want to mention that for the union to work, both tables fields in the query must be of the same data type in the order presented in the initial select query.

Since I don't think this is the case, looking at the data sample provided, union won't work.

My query should be changed, but only to reflect the different field in the query that is being selected

select a., b. from art a, xtra_art b where
a.id = b.art_id and a.main_group_id = $variable

[insomniac]

Hi,

Roger Ramjet,

i think that you got the picture, looking at your query, that should do the trick.

However, on the server there is a version 4.0.22 of mysql running, and it gave an error.

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON art.id = xart.art_id
WHERE B = 156 LIMI

Is this because of the mysql version?

Thanks for the tip about the ID fields. The other field names are not real, i've just made those up for you. The real ones are in Dutch. So Description is called "omschrijving" no problem there.

Greetings,

Jeroen.

[Roger_Ramjet]

Look at the table names and field names, should it not be xart_art.art_id

.....

Bastien, I don't give a tutorial every time I post a reply in these forums. Instead, I give a link to the relavent manual section. I expect everyone to look it up for themselves, otherwise they will never learn anything.

[insomniac]

Originally posted by Roger Ramjet
Look at the table names and field names, should it not be xart_art.art_id

.....

Bastien, I don't give a tutorial every time I post a reply in these forums. Instead, I give a link to the relavent manual section. I expect everyone to look it up for themselves, otherwise they will never learn anything.

The name of the table is xart (changed it, shorter is better).

for the complete package this is the real structure:

table artikelen:
id int(4)

art_nummer varchar(8)

int_groep varchar(20)

spec_groep_id varchar(20)

spec_subgroep_id varchar(20)

naam varchar(20)

kort mediumtext

lang longtext
irnummer varchar(10)

van varchar(10)

voor varchar(10)
time timestamp(14)

volgorde int(2)

table xart:
id int(4)

art_nummer varchar(8)

int_groep varchar(20)

spec_groep_id varchar(20)

spec_subgroep_id varchar(20)

this is my query where i want to check that art_nummer is in both tables where int_groep = 155

SELECT
artikelen.id as A,
artikelen.int_groep as B,
artikelen.spec_groep_id as C,
artikelen.spec_subgroep_id as D,
artikelen.naam as E,
artikelen.van as G
FROM artikelen WHERE B=155
UNION
SELECT
xart.id as A,
xart.int_groep as B,
xart.spec_groep_id as C,
xart.spec_subgroep_id as D,
artikelen.naam as E,
artikelen.van as G
FROM artikelen, xart ON artikelen.art_nummer=xart.art_nummer WHERE B=155

And then i got that error.

[Roger_Ramjet]

Hm! Yes, I always find the join syntax in mysql a bit dodgy, but then that's me. Try

FROM xart LEFT JOIN artikelen USING ( art_nummer) WHERE B=155

[insomniac]

Originally posted by Roger Ramjet
Hm! Yes, I always find the join syntax in mysql a bit dodgy, but then that's me. Try

FROM xart LEFT JOIN artikelen USING ( art_nummer) WHERE B=155

Thank you Roger Ramjet.

That did do the trick.

And now i do understand the select as and join syntax better.

Greetings,

Jeroen.