Joining two tables but the join key is in a query string
Question by lauthiamkok
I want to join these two table but the join key of the second table is in a query string,
page table,
page_id url
1 a
2 c
3 d
system table,
system_id query
1 page_id=1&content=on&image=on
2 type=post&page_id=2&content=on
as you can see that page_id is part of the query string in system table.
so how can I join them like the standard joining table method below?
SELECT*
FROM page AS p
LEFT JOIN system AS s
ON p.page_id = s.page_id
EDIT:
I def can change the system table into something like this,
system_id page_id query
1 1 page_id=1&content=on&image=on
2 2 type=post&page_id=2&content=on
3 NULL type=page
But the reason why I don’t want to do this is that the page_id is no need for many certain records. I don’t want make a column with too many null.
Answer by Nation
I guess you wanted something like this (MSSQL!):
DECLARE @query VARCHAR(50)
DECLARE @Lenght INT
DECLARE @PageID INT
SET @query = '4kkhknmnkpage_id=231&content=on&image=on'
SET @Lenght = PATINDEX('%&%', substring(@query,PATINDEX('%page_id=%', @query),50)) - 9
SET @PageID = CAST(SUBSTRING(@query,PATINDEX('%page_id=%', @query) + 8,@Lenght) AS INT)
SELECT @PageID -- you can do as you please now :)
OR:
SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTRING(query,PATINDEX('%page_id=%', query) + 8,(PATINDEX('%&%', substring(query,PATINDEX('%page_id=%', query),50)) - 9)) AS INT) AS page_id
FROM system) AS s
ON p.page_id = s.page_id
-- Do as you please again :)
I guess what you really wanted was something like this (MYSQL!):
SET @query := '4kkhknmnkpage_id=231&content=on&image=on';
SET @Lenght := POSITION('&' IN (SUBSTR(@query,POSITION('page_id=' IN @query),50))) - 9;
SET @PageID := CAST(SUBSTR(@query,POSITION('page_id=' IN @query) + 8,@Lenght) AS SIGNED );
SELECT @PageID
OR
SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTR(query,POSITION('page_id=' IN query) + 8,(POSITION('&' IN (SUBSTR(query,POSITION('page_id=' IN query),50))) - 9)) AS SIGNED) AS pageID
FROM system) AS s
ON p.page_id = s.pageID
Answer by Starx
Joining two tables without the common field and data type, is fundamentally wrong IMO.
I will suggest that you extract the page_id and insert it in the database and use a normal join to accomplish what you are searching for.
SO making the columns like
+------------+-----------+---------+
| system_id | page_id | query |
------------------------------------
Here is a snippet with which you are extract the page_id.
$query = 'page_id=1&content=on&image=on';
$queryParts = explode('&', $query);
$params = array();
foreach ($queryParts as $param) {
$item = explode('=', $param);
$params[$item[0]] = $item[1];
}
$page_id = $parems['page_id'];
Then you can go on with the insert and use simple join statement to solve your problem in a proper way.
Update:
Since you are able to change the schema to a feasible one. You dont need to worry about some rows having empty rows on this.
