• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Complex mySQL recordset

LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Hoping one of you mySQL gurus can help me with a bit of a pickle.

I have two tables:

1: Dogs

2: Litters

What I need to do is pull the male and female dog from the list of dogs.

Example in brief form:

Table One (Litters)

litters_id
litters_male

litters_female

Table two (Dogs)

parent_id

parent_name

parent_picture

parent_notes

etc ....

My input form would have two drop down fields listing the dogs ..

Male: (Dropdown Menu 1)
Famale (Dropdown Menu 2)

Each of these drops successfully put the parent_id value into the litters_male and litters_female respectively.

What I need to do is to display on a page from the litters recordest the dog names and other info related to each dog within the same recordset.

So I need to write a JOIN to join both the litters_male and litters_female to the parent_id field.

Is this possible?

Thanks in advance

Brad

TOPICS
Server side applications

Views

1.7K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

>So I need to write a JOIN to join both the litters_male

>and litters_female to the parent_id field.

This is not complex at all, it's very basic SQL. What you need to do is create an alias table. An alias table is just a reference to a physical table in the sql statement. It has the effect of acting like a distinct table. Then you can join the first parent table to the litter to get the male, and use the alias to get the female. To keep things clear, you could use an alias for both parents, call one male_parent and the other female_parent.

http://www.w3schools.com/sql/sql_alias.asp

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

So do I have to write 3 SELECT statements in the same query?

I can't seem to get my head around how this would look. Damn I wish Adobe wouldn't have killed ADDT .. QuB was awesome. 😉

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Am I on the right track?

SELECT litters.litter_id, litters.litter_breed, litters.litter_male, litters.litter_female, litters.litter_bred, litters.litter_due,litters.litter_notes,female_parents.parents_id,female_parents.parents_name, female_parents.parents_picture,male_parents.parents_id,male_parents.parents_name,male_parents.parents_picture
FROM parents AS female_parents, parents AS male_parents, litters
WHERE litters.litter_male = male_parents.parents_id AND litters.litter_female = female_parents.parents_id

I can get the 4 litter records to show with the data from the male dog, but the data for the female dog doesn't show up. I know I am either doing things completely wrong or I am missing something very simple.

Brad

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Brad, you are definitely on the right track. And in answer to your former question  - no you don't need 3 statements - but I think you figured that out already.

I don't see anything wrong initially with the statement. You might want to remove the parent id's and other extraneous columns from the first part of the statement. Just focus on getting the litter id and parent names. That will make the statement easier to read and debug.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Also, are you trying to get this to work with the dropdown now, or just testing the sql?  Make sure the basic SQL statement works before you try this in a script.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Also, are you trying to get this to work with the dropdown now, or just testing the sql?  Make sure the basic SQL statement works before you try this in a script.

No, this is just displaying the data. The initial data was entered for the dogs in the litter table from drop downs in the administration area thats it. The part I am trying to figure out is just straight data display.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

I need the parent Id though to link to that dog's profile page. And without the parent id in there I get hundres of records when there should only be 4. I can't see why it won't populate the second set of parent info. This is so confusing ... lol

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

>I need the parent Id though to link to that dog's profile page.


The columns you select from a table don't have an impact on the rows returned. That's controlled by the joins and other where clause conditions. Try this:

SELECT litters.litter_id, female_parents.parents_name, male_parents.parents_name
FROM parents AS female_parents, parents AS male_parents, litters
WHERE litters.litter_male = male_parents.parents_id AND litters.litter_female = female_parents.parents_id

If that doesn't work, then check your data and make sure the foreign key values are all correct.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Its still gives me the same issue ... gives me the male dogs name but not the female?

missingdatta.jpg

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Maybe if you see the actuall data structure ... ?

CREATE TABLE `litters` (
  `litter_id` tinyint(3) NOT NULL auto_increment,
  `litter_breed` tinyint(2) default NULL,
  `litter_male` tinyint(3) default NULL,
  `litter_female` tinyint(3) default NULL,
  `litter_bred` varchar(32) collate latin1_german2_ci default NULL,
  `litter_due` varchar(32) collate latin1_german2_ci default NULL,
  `litter_notes` varchar(255) collate latin1_german2_ci default NULL,
  PRIMARY KEY  (`litter_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `litters`
--

INSERT INTO `litters` VALUES (1, 1, 11, 9, 'December 4, 2009', 'February 5, 2010', '');
INSERT INTO `litters` VALUES (2, 1, 12, 7, 'January 2010', 'March 2010', '');
INSERT INTO `litters` VALUES (3, 1, 12, 5, 'January 2010', 'March 2010', '');
INSERT INTO `litters` VALUES (4, 2, 1, 2, 'January 2010', 'March 2010', '');

-- --------------------------------------------------------

--
-- Table structure for table `parents`
--

CREATE TABLE `parents` (
  `parents_id` tinyint(3) NOT NULL auto_increment,
  `parents_name` varchar(32) collate latin1_german2_ci default NULL,
  `parents_sex` varchar(10) collate latin1_german2_ci default NULL,
  `parents_breed` tinyint(2) default NULL,
  `parents_notes` text collate latin1_german2_ci,
  `parents_picture` varchar(64) collate latin1_german2_ci default NULL,
  `parents_thumb` varchar(64) collate latin1_german2_ci default NULL,
  PRIMARY KEY  (`parents_id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci COMMENT='parent profiles' AUTO_INCREMENT=19 ;

--
-- Dumping data for table `parents`
--

INSERT INTO `parents` VALUES (1, 'Kremlin', 'Male', 2, '<p><span class="bodytext"><p>Where do I start...Kremlin has got to be one of the most georgeous Dogue De Bordeaux''s you will ever lay eyes on!&#160; He has the pedigree to match as well!!&#160; Kremlin is 2 years old, was born at Riverbend Bordeaux Kennel in Kettle Falls Washington.&#160; Thanks you so much Jody for allowing us to make him part of our family as well as&#160;the&#160;foundation&#160;of our breeding program.&#160; We are extremely lucky!!&#160;</p> <p>Kremlin''s father was the famous Champion Riverbend''s Beefsteak.&#160; He weighed 166 pounds of solid male Dogue De Bordeaux!&#160; He is also world famous for his offspring producing many champions.&#160;&#160;Beefsteak''s&#160;father&#160;was&#160;Champion&#160;Beefy&#160;T.&#160;Bad&#160;Boy.&#160; Kremlin''s mother is Ella Van De Paterhoek who came straight from the world famous Van De Paterhoek Kennel in Belgium!&#160; She was out of their most famous breeding pair International Champion Emberez Apollo and Bintje De Paterhoek.&#160; His mother is also full sister to Champion Dillen Van De Paterhoek,&#160;Champion Effje&#160;Van&#160;De&#160;Paterhoek&#160;and the famous Champion Enferno Van De Paterhoek!!</p></span></p>', 'kremlin.jpg', NULL);
INSERT INTO `parents` VALUES (2, 'Sako', 'Female', 2, '<p><span class="bodytext">Sako is a Dogue De Bordeaux a.k.a French Mastiff.&#160; She was born in Washington on December 8, 2008.&#160; She has a very impressive background!! <br></span></p>', 'sako.jpg', NULL);
INSERT INTO `parents` VALUES (3, 'Anna Belle', 'Female', 1, '<p>"The Paw Palace Anna Supreme"<br><br>&#160;Anna is a beautiful and outstanding black and tan merle female! She was born in Louisiana on September 7, 2007. She weighs 4 lbs. Anna is a loving, playful and beautiful little girl with a very soft and vivid colored coat! <br><br>Anna is AKC and CKC registered!!</p>', 'annabelle.jpg', NULL);
INSERT INTO `parents` VALUES (4, 'Daisy', 'Female', 1, '<p>"Oneway Back From Iraq"<br><br>&#160;Daisy is white with cream colored markings. She was born in Wisconsin on July 27, 2006 . Daisy has the coby style body and weighs approx. 6 lbs. She is small but stocky! She also has a perfect little apple head and very loving personality! Daisy likes to mother and has taken on the responsibility of mothering all my other chi''s! <br><br>&#160;Daisy is Akc and CKC registered.<br><br>ONEWAY BACK FROM IRAQ Champions include<br><br>(4 generation): CH MT View''s Lil'' General</p>', 'daisy.jpg', NULL);
INSERT INTO `parents` VALUES (5, 'Journey', 'Female', 1, '<p><span class="bodytext"><p style="text-align: left;">"Journey Reins Supreme"</p> <p style="text-align: left;">This little sweetie comes to us from Missouri, She is AKC and CKC Registered.&#160; Her Father is AKC Champion Rebound Black Russian.&#160; He has 47 Champions out of 62 in his 5 generation pedigree!!!&#160; Amazing!!&#160; Her Mother is Davishall Lamars Wild Rose and almost every dog in her pedigree is Davishall bred so she is mostly stong Davishall foundation bloodlines.&#160; She should be show quality and should turn out to be a dream!!! ...</p></span></p>', 'journey.jpg', NULL);
INSERT INTO `parents` VALUES (7, 'Oakie', 'Female', 1, '<p><span class="bodytext"><p style="text-align: left;"><em>"Felina''s Oakie Supreme"</em></p> <p style="text-align: left;">Oakie is a blue and tan female. Her Birthday is January 16, 2007 and she was born in Oklahoma. She is approx 8 lbs, she looks irredescant in the sun! She is a short coat and has the cutest little deerhead. She is so laid back and easy going, loves letting the pups chew on her!</p> <p style="text-align: left;">She is AKC and CKC registered.&#160;</p></span></p>', 'oakie.jpg', NULL);
INSERT INTO `parents` VALUES (8, 'Tikka', 'Female', 2, '<p><span class="bodytext">Tikka is our newest addition...thanks again to Jody of Riverbend Bordeaux for this georgeous girl!! She was born July 8,2009 in Washington. Her mother is Mitzi and her father is Kid. Kid''s mother is Junior Champion Amy and his father is Champion/Junior Champion Debonaire. They are from Kennel Z Orisku from the Czech Republic.&#160; </span></p>', 'tikka.jpg', NULL);
INSERT INTO `parents` VALUES (9, 'Wistie', 'Female', 1, '', 'wistie.jpg', NULL);
INSERT INTO `parents` VALUES (10, 'Chico', 'Male', 1, '', 'chico.jpg', NULL);
INSERT INTO `parents` VALUES (11, 'Timber', 'Male', 1, '<p><span class="bodytext"><p style="text-align: left;">"Journey''s Supreme Tiny Timber Wolf"</p> <p style="text-align: left;">This tiny little man comes from Missouri and is AKC and CKC Registered! His Father is AKC Champion Rebound Black Russian.He has 47 Champions out of 62 in his 5 generation pedigree!!! Amazing!! His Mother is Ka-Lynn''s Pixie Dust at Lamar and has 5 champions out of 14 in her 3 generation pedigree.&#160; He is super TINY and should do a tremendous job as our next stud!!</p></span></p>', 'timber.jpg', NULL);
INSERT INTO `parents` VALUES (12, 'Yogi', 'Male', 1, '<p style="text-align: left;"><em>"JK Littlesbugs Yogi Nobody Did Nothin"</em><strong><span class="bodytext"> </span></strong><span class="bodytext"><p style="text-align: left;">We at Supreme Chihuahua proudly present to you our newest addition Yogi! He was born in Pennsylvania on June 18, 2006, and later moved to Orlando, Florida. From there he came to make his permanent home here with us at Supreme Chihuahua! We consider ourselves very fortunate to have aquired this beautiful and tiny one of a kind extreme applehead chihuahua stud! Yogi is a 2 lb 6 oz teeny tiny blue fawn with white markings.<span style="font-style: italic;"> </span><em>He has 55 CHAMPIONS in his 6 generation pedigree...WOW!! </em>We are very excited to see what he will produce here at Supreme Chihuahua...I know his pups will be tiny and awesome! You will have a hard time finding these perfect qualities anywhere else!</p><p style="text-align: left;">Yogi is AKC and CKC registered.</p><p style="text-align: left;"></p><p style="text-align: left;"><em>JK LITTLESBUGS YOGI NOBODY DID NOTHIN</em></p> <p style="text-align: left;"></p><p style="text-align: left;"><em>CHAMPIONS INCLUDE (4 GENERATION):</em></p> <p style="text-align: left;"></p><p style="text-align: left;"><em>CH Ostreet Twenty-Four Seven</em><br><em>CH Cam''s Buttontop Beau</em><br><em>CH Durbin''s Atom</em><br><em>CH Davishall Black Bark O''BK</em></p>   </span></p>', 'yogi.jpg', NULL);

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

Well, I'm stumped ... even using QuB I can't get the second dog details to show up ..... ARGH!

Brad

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

I have no idea. Some things to try

Remove the AS keyword from the aliases in the from clause. It's supposed to be optional but who knows, maybe there's a bug?

Alias the column names in the select - you should not need to but again, maybe a bug?

Try ANSI98 join syntax.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

For some reason I finally got it to work using:

SELECT parents_female.parents_picture AS parents_picture_1,
parents_female.parents_name AS parents_name_1,
parents_female.parents_id AS parents_id_1, litters.litter_male,
litters.litter_female, parents_male.parents_id,
parents_male.parents_name, parents_male.parents_picture,
litters.litter_breed, litters.litter_bred, litters.litter_due,
litters.litter_notes
FROM ((litters
LEFT JOIN parents AS parents_male ON parents_male.parents_id=litters.litter_male)
LEFT JOIN parents AS parents_female ON parents_female.parents_id=litters.litter_female)

Thanks for your time and help ... helped me get the right direction.

Brad

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 12, 2010 Jan 12, 2010

Copy link to clipboard

Copied

LATEST

You're welcome. It should not have been that difficult. Most DBMS's would have returned the correct result set with a much simpler select statement. I've never worked with MySQL so I'm not really familiar with its quirks. Glad you got it working.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines