SQL SELECT statemtent help?

Taneya

Can anyone help me on this? I have a SELECT statement that is not working correctly and I don't know how to fix it.

Here is some information on my database with only the fields in question listed.

Table: designers - and the fields are designerid, name
Table: magazines - and the fields are magid, magnameid
Table: magname - and the fields are magnameid, magname
Table: patterns - and the fields are title, designerid,magid,stitchcount,notes

Here is my statement:
SELECT patterns.title, designers.name, patterns.stitchcount, patterns.picture, patterns.notes
FROM patterns, designers, magname, magazines WHERE patterns.magid=6 AND patterns.designerid = designers.designerid AND magname.magnameid = magazines.magnameid AND magazines.magid = patterns.magid ORDER BY title ASC";

The problem I am having is when there is more than one design in the same issue of a magazine (magid is the id number for the magazine issue). If there are two designs by the same designer (designerid) then, only one of them is shown.

If I remove the references to the designer, then everything is okay, except, with this one, no information about the designer is given and I want the designer's name to appear in the results. Here is that statement:

SELECT patterns.title, patterns.stitchcount, patterns.picture, patterns.notes FROM patterns, magname, magazines WHERE patterns.magid=6 AND magname.magnameid = magazines.magnameid AND magazines.magid = patterns.magid ORDER BY title ASC";

Can anyone help me figure out the correct select statement so that i get ALL the issues in a specific magazine even if a designer has more than one design in it?

(oh, and if you're curious -my database is an index of designs from cross-stitch magazines 🙂 )

RoadKingHD

Would it be possible to do a mysqldump of your tables? If I had the database schema and a few rows of data it would be easier to test the sql statement...provided this is data you don't mind posting. You can edit the data dump and delete some of the INSERT statements if the data set is large. Upload the dump as a text file and I'll look at it for you!

mysqldump -u username -p databasename table1 table2 table3 > stich.sql

more info on mysqldump can be found in the manual at
[URL=http://]http://www.mysql.com/doc/en/mysqldump.html[/URL]

Kevin

Taneya

Kevin,

I've got the dump for you, but I am not sure how to "upload" the file here, I put it online. You can download it at

http://www.taneya-kalonji.com/dump/dump.txt

I will warn you though - this all the entries i've made up to this point. the table subcategories has the most entries (around 350), but you shouldn't need to worry about it as I'm not including it in my select statement.

And, if this helps, the one I noticed this problem with is in the design called Footprints by Caroline Palmer. When I do the query for designs from the magazine it is in (magid=6), only her other design appears in the results. Her other design that is in this same magazine is titled Assissi Cards.

Thanks!

RoadKingHD

I got the dump file and loaded into MySQL. I'll check it out.

RoadKingHD

OK, forgive me if I don't articulate well, take a look at the results I get when I run your SQL "as-is" and tell me if it is the same results you are getting.

I am seeing two results for Palmer, Caroline when magid=6.

http://www.jamminwebs.com/stich3_export.html😕

Taneya

okay. this is the weirdest thing.

I have just done another issue that has more than one design by the same designer and when I do this same query (replacing the magid number) it works. Also, I see from what you've done, it works.

However, in my results page I am still seeing only one design by Caroline Palmer.

So, i guess the problem is not in my select statement. I'm going to try and figure out if maybe i messed something up in my code ...

Thanks for your help!

taneya

RoadKingHD

So you think is is your code that has it out for Caroline?!

Don't you hate that?

Your SQL seems fine. Hope the debug process goes well!

Maybe something simple...it usually is.