Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 796
  • Last Modified:

Merging multiple rows

Would it be possible to merge multiple rows from 2 different tables?

Let's say that i have a global table: GLOBAL and a second table: OVERRIDE

The GLOBAL table will contain several columns.
The OVERRIDE table will only contain a few of the columns that are also present in the GLOBAL table.

Here is very clear example of what I'm trying to accomplish:

GLOBALS_TABLE{
      pid: 80,
      price: 100,
      image: 80i.jpg,
...
}

OVERRIDE_TABLE{
      pid: 80,
      price: 999
}

After running the query, i would get a merged result set such as this:

MERGED{
      pid: 80,
      price: 999,
      image: 80i.jpg
}:

So, once the query is executed, i would get the merged result set. I hope this doesn't confuse you. Let me know if you need more information.

0
_paulie
Asked:
_paulie
  • 6
  • 3
  • 2
1 Solution
 
Om PrakashCommented:
Try:
SELECT gt.id, isnull(ot.id, gt.id)
FROM GLOBALS_TABLE GT
LEFT JOIN OVERRIDE_TABLE OT ON GT.id = OT.id
0
 
_paulieAuthor Commented:
Tried that, i got: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ISNULL'

I tried IFNULL instead and MySQL returned 2 rows:

80 | 80
81 | 81

Hmm..

I want to do the following:

1) SELECT * FROM GLOBAL WHERE ID=80
2) SELECT * FROM OVERRIDE WHERE ID=80
3) MERGE BOTH RESULTS INTO ONE RESULT

OVERRIDE has a few columns with the same names as the GLOBAL table

I need a very basic MERGE.

To make myself even more clear, please take a look at this PHP function:

http://us.php.net/manual/en/function.array-merge.php

I know it's not related to MySQL but I want to make myself clear. Now after reading how that function functions, I want to do the same thing in MySQL. I want to Merge the first ROW found (GLOBALS) with the second ROW found (OVERRIDE). I want to OVERRIDE the GLOBAL record only for the columns found in the OVERRIDE TABLE.

That's kinda confusing, but I really hope you get my meaning.
0
 
Greg AlexanderLead DeveloperCommented:

Does this work for you?

SELECT * FROM GLOBAL A LEFT JOIN OVERRIDE B ON A.ID = B.ID;

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
_paulieAuthor Commented:
Yes, it works. However, I'm getting duplicate columns from both tables. Any idea on how to solve this?
0
 
Greg AlexanderLead DeveloperCommented:

How about group by:

SELECT * FROM GLOBAL A LEFT JOIN OVERRIDE B ON A.ID = B.ID GROUP BY A.ID;

Open in new window

0
 
_paulieAuthor Commented:
It's still returning 4 rows.

CREATE TABLE IF NOT EXISTS `test_globals_table` (
  `id` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `image` varchar(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `TEST_GLOBALS_TABLE` (`id`, `price`, `image`) VALUES
(80, 100, 'myimage.jpg'),
(81, 120, '81image.jpg');

CREATE TABLE IF NOT EXISTS `test_override_table` (
  `id` int(11) NOT NULL,
  `price` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test_override_table` (`id`, `price`) VALUES
(80, 999);

Open in new window


The following query
SELECT * FROM TEST_GLOBALS_TABLE A LEFT JOIN TEST_OVERRIDE_TABLE B ON A.ID = B.ID GROUP BY A.ID

Open in new window


returns:

id 	price 	image 	id 	price
80 	100 	myimage.jpg 	80 	999
81 	120 	81image.jpg 	NULL 	NULL

Open in new window


Expected result:

id 	price 	image 	
80 	999	myimage.jpg 	
81 	120 	81image.jpg

Open in new window


I hope this makes things clear. Thank you for your time!
0
 
_paulieAuthor Commented:
Any ideas?
0
 
Om PrakashCommented:
Use ifnull instead of isnull...

SELECT gt.id, IFNULL(ot.id, gt.id)
FROM GLOBALS_TABLE GT
LEFT JOIN OVERRIDE_TABLE OT ON GT.id = OT.id
0
 
_paulieAuthor Commented:
OK. can u please be kind to provide a working example with the tables i posted above? can u try to create a query that would give me the merged result as expected?
0
 
Om PrakashCommented:
hope this is clear
ppp.GIF
0
 
_paulieAuthor Commented:
That's excellent. would it be possible to do the same thing without manually setting the "IFNULL" for every column?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now